Bills were pulled from PTAXSIM amd summed to the pin level in files 1_...rmd and 2...rmd. Exemption data per PIN was pulled from PTAXSIM in file 3_.....rmd.
Code
ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "../ptaxsim.db/ptaxsim-2021.0.4.db")eq_factor <- DBI::dbGetQuery( ptaxsim_db_conn,"SELECT* FROM eq_factor ")muni_agency_names <- DBI::dbGetQuery( ptaxsim_db_conn,"SELECT DISTINCT agency_num, agency_name, minor_type FROM agency_info WHERE minor_type = 'MUNI' OR agency_num = '020060000' ")muni_tax_codes <- DBI::dbGetQuery( ptaxsim_db_conn,glue_sql(" SELECT DISTINCT agency_num, tax_code_num FROM tax_code WHERE agency_num IN ({muni_agency_names$agency_num*}) AND year = 2021 ",.con = ptaxsim_db_conn )) %>%mutate(tax_code_num =as.numeric(tax_code_num))tax_codes <- DBI::dbGetQuery( ptaxsim_db_conn,glue_sql(" SELECT DISTINCT tax_code_num, tax_code_rate FROM tax_code WHERE year = 2021 ",.con = ptaxsim_db_conn )) %>%mutate(tax_code_num =as.numeric(tax_code_num))
Code
taxbills_current <-read_csv("../Output/Dont_Upload/1_Get_All_Pins-CookPinTaxbills_2021_Actual.csv")# 22,453,875 tax bills in 2021 in municipalities. # taxing agency-PIN is the unit of observation here (only partial property tax bills)# Each PIN has multiple rows (because it is taxed by multiple taxing agencies)sapply(taxbills_current, function(x) sum(is.na(x)))# 1,825,816 billed properties with 14-digit PINs in incororated areas# 1,864,594 in incorported and unincorporated areas.pin14_bills_current <- taxbills_current %>%group_by(tax_code, class, pin) %>%mutate(total_bill = final_tax_to_dist + final_tax_to_tif) %>%# from each taxing agencysummarize(total_billed =sum(total_bill, na.rm =TRUE), # total on someone's property tax billav =first(av),eav =first(eav),# pin_count_in_parcel = n(),final_tax_to_dist =sum(final_tax_to_dist, na.rm =TRUE),final_tax_to_tif =sum(final_tax_to_tif, na.rm =TRUE),tax_amt_exe =sum(tax_amt_exe, na.rm =TRUE), # revenue lost due to exemptionstax_amt_pre_exe =sum(tax_amt_pre_exe, na.rm =TRUE), # total rev before all exemptionstax_amt_post_exe =sum(tax_amt_post_exe, na.rm =TRUE), # total rev after all exemptionsrpm_tif_to_cps =sum(rpm_tif_to_cps, na.rm =TRUE), # not usedrpm_tif_to_rpm =sum(rpm_tif_to_rpm, na.rm=TRUE), # not usedrpm_tif_to_dist =sum(rpm_tif_to_dist, na.rm=TRUE), # not usedtif_share =mean(tif_share, na.rm=TRUE), # not used ) %>%mutate(propclass_1dig =str_sub(class, 1, 1))# head(pin14_bills_current)sapply(pin14_bills_current, function(x) sum(is.na(x)))# Now group again and sum values for tax code and each property class!# There are 28,381 property class - tax code group combinations in incorporated areas.# There are 28,534 property class - tax code group combos in incorporated + unincorporated areasrm(taxbills_current)
Need to merge PIN level data of tax bills and exemptions per PIN if the goal is to calculate new tax rates for different scenarios AND view individual level tax bill changes. (i.e. if we want to look at the “median taxpayer” then need to use this method).
Note: The EAV from the exemption table is the original EAV (AV * equalizer) without consider TIFs or exemptions.
31,000 properties have $0 tax bills based on exemption data table made from PTAXSIM’s pin table. (Using the tax bill data allows for more in depth look at tax bills and can create variables such as small_bill if bills were less than $100 after exemptions were applied).
Calculate composite tax rates at municipal level.
- new_taxable_eav is is the current taxable EAV + the GHE exempt EAV. The taxrates calculated in this file are for GHE removal only. Other exemption types are still assumed to exist.
The assessed value and original equalized assessed values come from the pin data table within PTAXSIM. This table also has every type of exemption that the property received and the amount of EAV that was exempt due to the exemption.
Download 4C_joined_PINs from Box link here:. I would move the downloaded file into your R project and then the code below should work without changing the file path.
Table 2.2: Measure of the middle for Major Class 2 Properties
(a)
Calculates statistics from PINs that remain after removing properties
that received other exemption types. Median and mean statistics for
Class 200 - 299 properties (i.e. Major Class 2 Residential
Properties
677,602 residential properties have the GHE exemption and no other exemption types. 834,795 properties (residential and nonresidential) do not have exemption types.
around 350,000 PINs have at least one other exemption type that is not the GHE.
Calculate the composite tax rate (current and hypothetical from eliminating GHE) and merge in other key variables to the pin level data:
pin_data2 %>%filter((class >199& class <300) & agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%group_by(agency_name) %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +# geom_vline(aes(xintercept = median(av, na.rm=TRUE), color = "red")) +theme_classic()+theme(legend.position ="none") +# geom_text(aes(x=(median(av) + 5000), y = 1500, label = median(av))) +scale_x_continuous(limits =c(-5, 50000)) +# scale_y_continuous(limits = c(0, 1500))+labs(title ="PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs") +facet_wrap(~agency_name)
Code
pin_data2 %>%filter((class >199& class <300) & agency_name =="VILLAGE OF PHOENIX") %>%group_by(agency_name) %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +geom_vline(aes(xintercept =median(av, na.rm=TRUE), color ="red")) +geom_vline(aes(xintercept =7974), linetype ="dashed", label ="Breakeven AV") +theme_classic()+theme(legend.position ="none") +geom_text(aes(x=(median(av)), y =450, label =median(av))) +geom_text(aes(x=14000, y =350), label ="Breakeven AV = $7,974") +scale_x_continuous(limits =c(-5, 50000)) +# scale_y_continuous(limits = c(0, 1500))+labs(title ="Phoenix PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs",caption ="Median AV is $3,502, Breakeven AV is $7,974")
Code
pin_data2 %>%filter((class >199& class <300) & agency_name =="CITY OF CHICAGO") %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +geom_vline(aes(xintercept =median(av, na.rm=TRUE), color ="red")) +geom_vline(aes(xintercept =84228), linetype ="dashed") +theme_classic()+theme(legend.position ="none") +geom_text(aes(x=median(av)+20000, y =300000, label =median(av))) +geom_text(aes(x=100000, y =200000), label ="Breakeven AV = $84,228") +scale_x_continuous(limits =c(-5, 500000), labels = scales::dollar) +scale_y_continuous(limits =c(0, 300000))+labs(title ="Chicago PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs",caption ="Median AV is ~$21,000, Breakeven AV is ~$84,000")
2.1.1 How many homeowners would have decreased taxbills if the GHE were eliminated?
Code
pin_data2 %>%filter((class >199& class <300) ) %>%left_join(muni_taxrates, by ="agency_name") %>%left_join(muni_breakeven_points, by ="clean_name") %>%filter(pin_AV > nochange_av_munirates) %>%group_by(clean_name, has_HO_exemp) %>%summarize(n()) %>%mutate(has_HO_exemp =ifelse(has_HO_exemp ==0, "Did not claim", "Claimed GHE"))
Table 2.3:
Count of PINs with AV > breakeven point. Property classes
200-299.
Code
pin_data2 %>%filter((class >199& class <300) & (has_HO_exemp ==1)) %>%left_join(muni_taxrates, by ="agency_name") %>%left_join(muni_breakeven_points, by ="clean_name") %>%filter(pin_AV > nochange_av_munirates) %>%group_by(agency_name) %>%summarize(pins_over_breakpoint =n()) %>%left_join(muni_taxrates) %>%mutate(pct_c2_benefits =round((pins_over_breakpoint / C2_pins_in_muni)*100, digits =4)) %>%select(clean_name, pins_over_breakpoint, pct_c2_benefits)
Table 2.4:
Count of PINs with AV > breakeven point. Property classes 200 -
299 that claimed GHE in 2021
Count of PINs where current tax bill < hypothetical tax bill
Code
## Number of residential PINs that would have a lower tax bill in each munihomeowners_benefited <- pin_data2 %>%filter(class >199& class <300) %>%# filter keeps obs if they didn't claim the exemption OR they did claim it & have fancy houses# filter(has_HO_exemp == 0 | (has_HO_exemp == 1 & pin_AV > nochange_av)) %>%filter(# did not claim the general homeowner exemption (GHE) has_HO_exemp ==0|# OR # did claim the GHE in 2021, but their new bill still goes down compared # to than their current tax bill if the GHE were removed # implies that the hypothetical taxrate change*eav > exempt EAV * current tax rate (has_HO_exemp ==1& (cur_comp_TC_rate*(eav-exe_homeowner) > (new_comp_TC_rate*eav)))) %>%group_by(agency_name, has_HO_exemp) %>%summarize(lowerbill_PINcount =n()) %>%left_join(muni_taxrates, by ="agency_name") %>%mutate(lowerbill_PINcount =ifelse(is.na(lowerbill_PINcount), 0, lowerbill_PINcount)) %>%pivot_wider(id_cols =c(clean_name, C2_pins_in_muni), names_from = has_HO_exemp, values_from = lowerbill_PINcount, ) %>%rename(didnot_claim =`0`,lowerbill_did_claim_GHE =`1`) %>%mutate(lowerbill_did_claim_GHE =ifelse(is.na(lowerbill_did_claim_GHE), 0, lowerbill_did_claim_GHE),lowerbill_PINcount = didnot_claim + lowerbill_did_claim_GHE,pct_c2_w_higherbills =100-round((lowerbill_PINcount / C2_pins_in_muni)*100, digits =2),pct_c2_w_lowerbills =round((lowerbill_PINcount / C2_pins_in_muni)*100, digits =2),pct_c2_lowerbills_did_claim =round((lowerbill_did_claim_GHE / C2_pins_in_muni)*100, digits =2),pct_c2_lowerbills_didnotclaim =round((didnot_claim / C2_pins_in_muni)*100, digits =2))# homeowners_benefited
nobillchange <- muni_taxrates %>%left_join(muni_breakeven_points, by ="clean_name") %>%rename(nobillchange_propertyEAV = nobillchange_propertyEAV_munirates,nochange_av = nochange_av_munirates)nobillchange %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA", "CITY OF CHICAGO")) %>%ggplot(aes(y=C2median_av, x = agency_name)) +geom_col()+geom_text(aes(y=C2median_av +3000, label =round(C2median_av) ) ) +scale_y_continuous(labels = scales::dollar) +theme_classic() +scale_x_discrete(label =c("Chicago","Park Forest", "Phoenix", "Riverdale", "Winnetka")) +labs(y ="Median Residential AV", x ="", title ="Median Residential Property Assessed Value - All Class 2 property Types")nobillchange %>%filter(agency_name %in%c("CITY OF CHICAGO", "VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%ggplot(aes(y=(nobillchange_propertyEAV/eq_2021), x = agency_name)) +geom_col() +geom_text(aes(y=(nobillchange_propertyEAV/eq_2021) +3000, label =round(nobillchange_propertyEAV/eq_2021)) ) +scale_y_continuous(labels = scales::dollar) +theme_classic() +scale_x_discrete(label =c("Chicago", "Park Forest", "Phoenix", "Riverdale", "Winnetka")) +labs(y ="Breakeven Point - AV", x ="", title ="Residential Property AV Breakeven Point", caption ="Residential properties above these values would have their bills decrease if the GHE were eliminated (if they had claimed the GHE before)")
Code
nochange <- C2_munistats %>%left_join(muni_taxrates) %>%left_join(muni_breakeven_points) %>%rename(nobillchange_propertyEAV = nobillchange_propertyEAV_munirates,nochange_av = nochange_av_munirates)nochange %>%# filter(nochange_av < 300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +# geom_smooth(method = "lm" )+geom_text(aes(y = (nochange_av-5000), x = (C2median_av)), size =2)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none")+labs(y =" Breakeven Point", x ="Median AV - Class 2 Properties in Municipality", title ="Some highly valued homes would have lower tax bills if the GHE were eliminated", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated.")
Figure 2.1: Includes all municipalities
Code
nochange %>%filter(nochange_av <300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +geom_point(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale","Dolton"))), aes(y = nochange_av, x = C2median_av, color ="red"), size =3) + ggrepel::geom_label_repel(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale", "Dolton"))), aes(y = (nochange_av), x = (C2median_av)), size =3)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none") +labs(title ="Municipalities' Median AV & Breakeven Point - Class 2 Properties Only",y ="Breakeven Point", x ="Median AV")
Figure 2.2: Excludes outliers: labeled
Code
nochange %>%filter(nochange_av <300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +geom_point(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale","Dolton"))), aes(y = nochange_av, x = C2median_av, color ="red"), size =3) +#ggrepel::geom_text_repel(aes(label = clean_name), vjust = -1,colour="black") + # geom_smooth(method = "lm" )+# ggrepel::geom_text_repel(data = (nochange %>% filter(clean_name %in% c("Park Forest", "Chicago", "Winnetka", "Riverdale", "Dolton"))), aes(y = (nochange_av), x = (C2median_av)), size = 3)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none") +labs(title ="Municipalities' Median AV & Breakeven Point - Class 2 Properties Only",y ="Breakeven Point", x ="Median AV", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated. Excludes outliers: University Park, Bedford Park, McCook, Hodgkins, and Rosemont. Class 2 properties that had claimed the GHE would have lower taxbills even if the GHE were removed if the AV is above the breakeven point.")
Figure 2.3: Excludes outliers
Code
nochange %>%filter(clean_name %in%c("Phoenix", "Park Forest", "Riverdale")) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point() +geom_text(aes(y = (nochange_av-500), x = (C2median_av-100)), size =3)+theme_classic() +scale_x_continuous(labels = scales::dollar, limits =c(0,10000)) +scale_y_continuous(labels = scales::dollar, limits =c(0,20000)) +labs(title ="Municipalities' Median AV & Breakeven Point",y =" Breakeven Point", x ="Median AV", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated. Excludes outliers: University Park, Bedford Park, McCook, Hodgkins, and Rosemont. Class 2 properties that had claimed the GHE would have lower taxbills even if the GHE were removed if the AV is above the breakeven point.")
Figure 2.4: Do tax bills still counter-intuitively fall when exemptions are eliminated: The case of Phoenix, Park Forest, and Riverdale
Code
# nochange %>%# filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE") ) %>%# filter(is.finite(nobillchange_propertyEAV) )%>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# geom_point()+# geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+500)), size = 2) +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# theme_classic() +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners have lower tax bills if the GHE exemption was ended?",# subtitle = "Yes, in very, very rare cases. ")# # # nochange %>%# # filter(agency_name == "VILLAGE OF WINNETKA") %>%# filter(median_eav > 150000) %>%# filter(is.finite(nobillchange_propertyEAV) ) %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# #filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# geom_point()+# geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 2) +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# theme_classic() +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners with GHE benefit from removal of the GHE?",# caption = "A $500K home in Winnetka would benefit if everyone else lost their General Homestead Exemption")# nochange %>%# filter(is.finite(nobillchange_propertyEAV) ) %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# # geom_smooth(method = "lm" )+# geom_text(aes(x = (nobillchange_propertyEAV), y = (median_eav+1000)), size = 2)+# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners who claim GHE benefit from removal of the GHE?",# subtitle = "In very, very rare instances: yes. ")# # nochange %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(y=rate_change, x = median_eav, label=clean_name)) +# geom_point() +# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# labs(x= "EAV of Class 2 Property w/ No Bill Change", y = "Composite Tax Rate Change (Percentage Points)", title = "EAV of Residential Property 'Breakeven Point' & Composite Tax Rate Change # from Elimination of GHE")nochange %>%select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%ggplot(aes(y=rate_change, x = C2median_av, label=clean_name)) +geom_point() +theme_classic() +scale_x_continuous(labels = scales::dollar) +labs(x="AV of Class 2 Property w/ No Bill Change", y ="Composite Tax Rate Change (Percentage Points)", title ="AV of Residential Property 'Breakeven Point' & Composite Tax Rate Change from Elimination of GHE")
Code
# # nochange %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# ggplot(aes(y=rate_change, x = C2median_av*10, label=agency_name)) +# geom_point() +# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# labs(x= "Market Value of Class 2 Property # at 'Breakeven Point'", # y = "Composite Tax Rate Change # (Percentage Points)", # title = "Market Value of Residential Property 'Breakeven Point' & # Composite Tax Rate Change from Elimination of GHE")
2.2 Class 2 Summary Tables
Code
tax_bill_change_HO <- pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))tax_bill_change_HO
Code
pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%mutate(median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))
Code
## Grouped by if they have a $0 tax bill and had the GHE per munipin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%#filter(class == 203) %>% filter(class >199& class <300) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, zero_bill, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))
2.2.1 Calculation for Median properties in each munis
Code
## Grouped by if they have a $0 tax bill and had the GHE per muni## Recalculating for Josh & Rachaelmuni_median_summarytable <- pin_data2 %>%# filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%#filter(class == 203) %>% filter(class >199& class <300) %>%# merge in muni residential median AVleft_join(C2_munistats_filtered) %>%# +/- 500 from municpalities median residential AVfilter(av+500> median_av | av-500< median_av) %>%# Removes properties that received other types of exemptionsfilter(exe_senior ==0& exe_freeze ==0& exe_longtime_homeowner ==0& exe_disabled ==0& exe_vet_returning ==0& exe_vet_dis_lt50 ==0& exe_vet_dis_50_69 ==0& exe_vet_dis_ge70 ==0& exe_abate ==0) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, # zero_bill, has_HO_exemp ) %>%summarize(AV =median(median_av),`Taxable EAV`=median(eav),bill_cur =round(median(bill_current)),bill_new =round(median(bill_noexemps)),bill_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe)) %>%# merge in clean_names variableleft_join(nicknames) %>%select(clean_name, has_HO_exemp, bill_cur, bill_new, bill_change, perceived_savings, AV, `Taxable EAV`, everything())
2.3 Tax Bill Distributions
Code
pin_data2 %>%filter(major_class_code ==2& bill_current <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Actual Tax Bills With Current Exemptions", y ="Number of Tax Bills")pin_data2 %>%group_by(has_HO_exemp) %>%filter(major_class_code ==2& bill_current <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Actual Tax Bills With Current Exemptions", y ="Number of Tax Bills") +facet_wrap(~has_HO_exemp)pin_data2 %>%filter(major_class_code ==2& bill_noexemps <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills")
Code
pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000)) +scale_y_continuous(limits =c(0, 600))+labs(title="PHOENIX's Current Tax Bill Distribution", x ="Dollars", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000)) +scale_y_continuous(limits =c(0, 600))+labs(title="PHOENIX's Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills")
Figure 2.5: ?(caption)
Figure 2.6: ?(caption)
Code
pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +# scale_x_continuous(limits = c(-5,20000)) +# scale_y_continuous(limits = c(0, 80000)) +labs(title="WINNETKA's Actual Tax Bills With Current Exemptions", x="Dollars", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +#scale_x_continuous(limits = c(-5,20000)) + #scale_y_continuous(limits = c(0, 80000)) +labs(title="WINNETKA's Tax Bills Without General Homestead Exemptions", x="Dollars", y ="Number of Tax Bills")
Figure 2.7: ?(caption)
Figure 2.8: ?(caption)
Code
pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +# scale_y_continuous(limits = c(0, 2000))+labs(title="Park Forest's Change in Tax Bills", subtitle ="Hypothetical with no General Homestead Exemptions", x ="Change in Tax Bill ($)", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +# scale_x_continuous(limits = c(-2800,2800)) + # scale_y_continuous(limits = c(0, 2000))+labs(title ="PHOENIX's Change in Tax Bills", subtitle ="Hypothetical with no General Homestead Exemptions", x ="Change in Tax Bill ($)", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +labs(title="WINNETKA's Change in Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills", x ="Dollars")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF RIVERDALE") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +labs(title="RIVERDALE's Change in Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills", x ="Dollars")
Figure 2.9: ?(caption)
Figure 2.10: ?(caption)
Figure 2.11: ?(caption)
Figure 2.12: ?(caption)
Code
pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, zero_bill, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA","VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())
---title: 'Breakeven Points and Tax Bill Distributions'author: "AWM"date: "`r Sys.Date()`"format: html: df-print: paged code-fold: true code-download: true toc: true toc-location: left fig-cap-location: top---**Steps for prepping data for graphs below:**```{r setup, warning=FALSE, message=FALSE, include=FALSE}knitr::opts_chunk$set(echo =TRUE, warning =FALSE, message =FALSE)library(tidyverse)library(data.table)library(gstat)library(ptaxsim)library(glue)library(flextable) # for formatted table output into Word documentslibrary(kableExtra)nicknames <- readxl::read_excel("../Necessary_Files/muni_shortnames.xlsx")```Bills were pulled from PTAXSIM amd summed to the pin level in files `1_...rmd` and `2...rmd`. Exemption data per PIN was pulled from PTAXSIM in file `3_.....rmd`.```{r}ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "../ptaxsim.db/ptaxsim-2021.0.4.db")eq_factor <- DBI::dbGetQuery( ptaxsim_db_conn,"SELECT* FROM eq_factor ")muni_agency_names <- DBI::dbGetQuery( ptaxsim_db_conn,"SELECT DISTINCT agency_num, agency_name, minor_type FROM agency_info WHERE minor_type = 'MUNI' OR agency_num = '020060000' ")muni_tax_codes <- DBI::dbGetQuery( ptaxsim_db_conn,glue_sql(" SELECT DISTINCT agency_num, tax_code_num FROM tax_code WHERE agency_num IN ({muni_agency_names$agency_num*}) AND year = 2021 ",.con = ptaxsim_db_conn )) %>%mutate(tax_code_num =as.numeric(tax_code_num))tax_codes <- DBI::dbGetQuery( ptaxsim_db_conn,glue_sql(" SELECT DISTINCT tax_code_num, tax_code_rate FROM tax_code WHERE year = 2021 ",.con = ptaxsim_db_conn )) %>%mutate(tax_code_num =as.numeric(tax_code_num))``````{r eval = FALSE}taxbills_current <-read_csv("../Output/Dont_Upload/1_Get_All_Pins-CookPinTaxbills_2021_Actual.csv")# 22,453,875 tax bills in 2021 in municipalities. # taxing agency-PIN is the unit of observation here (only partial property tax bills)# Each PIN has multiple rows (because it is taxed by multiple taxing agencies)sapply(taxbills_current, function(x) sum(is.na(x)))# 1,825,816 billed properties with 14-digit PINs in incororated areas# 1,864,594 in incorported and unincorporated areas.pin14_bills_current <- taxbills_current %>%group_by(tax_code, class, pin) %>%mutate(total_bill = final_tax_to_dist + final_tax_to_tif) %>%# from each taxing agencysummarize(total_billed =sum(total_bill, na.rm =TRUE), # total on someone's property tax billav =first(av),eav =first(eav),# pin_count_in_parcel = n(),final_tax_to_dist =sum(final_tax_to_dist, na.rm =TRUE),final_tax_to_tif =sum(final_tax_to_tif, na.rm =TRUE),tax_amt_exe =sum(tax_amt_exe, na.rm =TRUE), # revenue lost due to exemptionstax_amt_pre_exe =sum(tax_amt_pre_exe, na.rm =TRUE), # total rev before all exemptionstax_amt_post_exe =sum(tax_amt_post_exe, na.rm =TRUE), # total rev after all exemptionsrpm_tif_to_cps =sum(rpm_tif_to_cps, na.rm =TRUE), # not usedrpm_tif_to_rpm =sum(rpm_tif_to_rpm, na.rm=TRUE), # not usedrpm_tif_to_dist =sum(rpm_tif_to_dist, na.rm=TRUE), # not usedtif_share =mean(tif_share, na.rm=TRUE), # not used ) %>%mutate(propclass_1dig =str_sub(class, 1, 1))# head(pin14_bills_current)sapply(pin14_bills_current, function(x) sum(is.na(x)))# Now group again and sum values for tax code and each property class!# There are 28,381 property class - tax code group combinations in incorporated areas.# There are 28,534 property class - tax code group combos in incorporated + unincorporated areasrm(taxbills_current)```Need to merge PIN level data of tax bills and exemptions per PIN if the goal is to calculate new tax rates for different scenarios AND view individual level tax bill changes. (i.e. if we want to look at the "median taxpayer" then need to use this method).Note: The EAV from the exemption table is the original EAV (AV \* equalizer) without consider TIFs or exemptions.31,000 properties have \$0 tax bills based on exemption data table made from PTAXSIM's `pin` table. (Using the tax bill data allows for more in depth look at tax bills and can create variables such as `small_bill` if bills were less than \$100 after exemptions were applied).```{r eval = FALSE}pin_eav <-read_csv("../Output/Dont_Upload/3_Exemption_Details_output-all_cook_pin_exemptions_2021_actual.csv") %>%#filter(class_code !=0) %>%mutate(all_exemptions = exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner + exe_disabled + exe_vet_returning + exe_vet_dis_lt50 + exe_vet_dis_50_69 + exe_vet_dis_ge70 + exe_abate) %>%mutate(zero_bill =ifelse(eav <= all_exemptions, 1, 0),has_HO_exemp =ifelse(exe_homeowner >0, 1, 0)) %>%rename(equalized_AV = eav,pin_AV = av)# table(pin_eav$zero_bill)table(pin_eav$major_class_code, pin_eav$zero_bill)# table(pin_eav$major_class_code, pin_eav$has_HO_exemp)## Add exemption types and values to the tax bill data at PIN levelpin_data <-left_join(pin14_bills_current, pin_eav, by =c("pin", "class"="class_code" ))## Add tax code tax rate to PIN level datapin_data <-left_join(pin_data, tax_codes, by =c("tax_code"="tax_code_num"))taxcodes_current <- pin_data %>%group_by(tax_code) %>%summarize(av =sum(av),eav =sum(eav),equalized_AV =sum(equalized_AV),pins_in_class =n(),current_exemptions =sum(all_exemptions),HO_exemps =sum(exe_homeowner),tax_code_rate =first(tax_code_rate), final_tax_to_dist =sum(final_tax_to_dist, na.rm =TRUE), # used as LEVY amount!! final_tax_to_tif =sum(final_tax_to_tif, na.rm =TRUE),tax_amt_exe =sum(tax_amt_exe, na.rm =TRUE), tax_amt_pre_exe =sum(tax_amt_pre_exe, na.rm =TRUE), tax_amt_post_exe =sum(tax_amt_post_exe, na.rm =TRUE),rpm_tif_to_cps =sum(rpm_tif_to_cps, na.rm =TRUE), # not usedrpm_tif_to_rpm =sum(rpm_tif_to_rpm, na.rm=TRUE), # not usedrpm_tif_to_dist =sum(rpm_tif_to_dist, na.rm=TRUE), # not usedtif_share =mean(tif_share, na.rm=TRUE), # not used ) %>%mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%rename(cur_comp_TC_rate = tax_code_rate) %>%mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),new_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + HO_exemps) %>%mutate(new_comp_TC_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%mutate(new_comp_TC_rate =ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>%select(tax_code, cur_comp_TC_rate, new_comp_TC_rate, current_taxable_eav, new_taxable_eav, everything())#head(taxcodes_current) taxcode_taxrates <- taxcodes_current %>%select(tax_code, cur_comp_TC_rate, new_comp_TC_rate, current_exemptions, HO_exemps)pin_data2 <-left_join(pin_data, taxcode_taxrates, by =c("tax_code"="tax_code"))pin_data2 <- pin_data2 %>%left_join(muni_tax_codes) pin_data2 <- pin_data2 %>%left_join(muni_agency_names)# write_csv(pin_data2, "../Output/4C_joined_PINs_bills_and_exemptions.csv")```Calculate composite tax rates at municipal level.\- `new_taxable_eav` is is the current taxable EAV + the GHE exempt EAV. The taxrates calculated in this file are for GHE removal only. Other exemption types are still assumed to exist.The assessed value and original equalized assessed values come from the `pin` data table within PTAXSIM. This table also has every type of exemption that the property received and the amount of EAV that was exempt due to the exemption.> Download 4C_joined_PINs from [Box link here:](https://uofi.box.com/s/cvp7pdjkx8kth7o0vg9itpra3z8vfkzq). I would move the downloaded file into your R project and then the code below should work without changing the file path.```{r read-in-data}pin_data2 <-read_csv("../Output/4C_joined_PINs_bills_and_exemptions.csv")pin_data2 <- pin_data2 %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noGHE = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_noexemps = new_comp_TC_rate/100*(equalized_AV),bill_change = bill_noGHE - bill_current)``````{r class2-stats}#| label: tbl-class2-stats#| tbl-cap: "Measure of the middle for Major Class 2 Properties"#| column: screen-insetC2_munistats <- pin_data2 %>%filter(class >199& class <300) %>%group_by(agency_name) %>%arrange(av) %>%summarize(median_eav =round(median(eav)), median_av =round(median(av)), avg_av =round(mean(av)),avg_eav =round(mean(eav)),C2_pins_in_muni =n(),C2_current_exemptions =sum(all_exemptions, na.rm =TRUE),C2_HO_exemps =sum(exe_homeowner, na.rm =TRUE), ) C2_munistats%>%left_join(nicknames) %>%select(clean_name, everything()) %>%select(-c(agency_name, agency_number))``````{r}#| label: tbl-filtered-munistats#| tbl-cap: "Measure of the middle for Major Class 2 Properties"#| tbl-subcap: "Calculates statistics from PINs that remain after removing properties that received other exemption types. Median and mean statistics for Class 200 - 299 properties (i.e. Major Class 2 Residential Properties"## removes properties that have more than one exemption typeC2_munistats_filtered <- pin_data2 %>%filter(class >199& class <300) %>%filter(exe_senior ==0& exe_freeze ==0& exe_longtime_homeowner ==0& exe_disabled ==0& exe_vet_returning ==0& exe_vet_dis_lt50 ==0& exe_vet_dis_50_69 ==0& exe_vet_dis_ge70 ==0& exe_abate ==0) %>%group_by(agency_name) %>%arrange(av) %>%summarize(median_eav =round(median(eav, na.rm=TRUE)), median_av =round(median(av, na.rm=TRUE)), avg_av =round(mean(av, na.rm=TRUE)),avg_eav =round(mean(eav, na.rm=TRUE)),C2_pins_in_muni =n(),C2_current_exemptions =sum(all_exemptions, na.rm =TRUE),C2_HO_exemps =sum(exe_homeowner, na.rm =TRUE), ) C2_munistats_filtered %>%left_join(nicknames) %>%select(clean_name, everything()) %>%select(-c(agency_name, agency_number))```677,602 residential properties have the GHE exemption and no other exemption types. 834,795 properties (residential and nonresidential) do not have exemption types.around 350,000 PINs have at least one other exemption type that is not the GHE.Calculate the composite tax rate (current and hypothetical from eliminating GHE) and merge in other key variables to the pin level data:### Calculating composite tax rates```{r}eq_2021 <-3.0027muni_taxrates <- pin_data2 %>%ungroup() %>%group_by(agency_name) %>%arrange(av) %>%summarize(muni_median_av =round(median(av, na.rm=TRUE)),muni_median_eav =round(median(eav, na.rm=TRUE)),muni_mean_av =round(mean(av, na.rm=TRUE)),muni_mean_eav =round(mean(eav, na.rm=TRUE)),av =sum(av, na.rm =TRUE),eav =sum(eav, na.rm =TRUE),equalized_AV =sum(equalized_AV, na.rm =TRUE),pins_in_muni =n(),current_exemptions =sum(all_exemptions, na.rm =TRUE),HO_exemps =sum(exe_homeowner, na.rm =TRUE),tax_code_rate =mean(tax_code_rate, na.rm =TRUE), # Changed from first() to mean() on Nov 1final_tax_to_dist =sum(final_tax_to_dist, na.rm =TRUE), # used as LEVY amount!! final_tax_to_tif =sum(final_tax_to_tif, na.rm =TRUE),tax_amt_exe =sum(tax_amt_exe, na.rm =TRUE), tax_amt_pre_exe =sum(tax_amt_pre_exe, na.rm =TRUE), tax_amt_post_exe =sum(tax_amt_post_exe, na.rm =TRUE),rpm_tif_to_cps =sum(rpm_tif_to_cps, na.rm =TRUE), # not usedrpm_tif_to_rpm =sum(rpm_tif_to_rpm, na.rm=TRUE), # not usedrpm_tif_to_dist =sum(rpm_tif_to_dist, na.rm=TRUE), # not usedtif_share =mean(tif_share, na.rm=TRUE), # not used ) %>%mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%rename(cur_comp_muni_rate = tax_code_rate) %>%mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_muni_rate/100),new_taxable_eav = final_tax_to_dist/(cur_comp_muni_rate/100) + HO_exemps) %>%mutate(new_comp_muni_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%mutate(new_comp_muni_rate =ifelse(is.nan(new_comp_muni_rate), cur_comp_muni_rate, new_comp_muni_rate)) %>%left_join(C2_munistats) %>%left_join(nicknames) %>%mutate(rate_change = cur_comp_muni_rate - new_comp_muni_rate,# nobillchange_propertyEAV = round(10000 * ((cur_comp_muni_rate/100) / (rate_change/100))),# nochange_av = round(nobillchange_propertyEAV / eq_2021),# nochange_ratio = nochange_av / median_av ) %>%select(clean_name, C2median_av = median_av, muni_median_av, C2mean_av = avg_av, muni_mean_av, cur_comp_muni_rate, new_comp_muni_rate, current_taxable_eav, new_taxable_eav, everything())muni_taxrates #muni_taxrates %>% write_csv("../Output/4C_muni_taxrates.csv")muni_taxrates %>%left_join(nicknames) %>%select(clean_name, muni_median_av, C2median_av )``````{r eval=FALSE, include = FALSE}# # # pin_data2 %>% # filter(class > 199 & class < 300) %>%# filter(exe_senior == 0 & # exe_freeze == 0 & # exe_longtime_homeowner == 0 & # exe_disabled == 0 & # exe_vet_returning == 0 & # exe_vet_dis_lt50 == 0 & # exe_vet_dis_50_69 == 0 & # exe_vet_dis_ge70 == 0 & # exe_abate == 0) %>%# # select(-c(cur_comp_TC_rate, new_comp_TC_rate)) %>%# left_join(muni_taxrates) %>% # mutate(bill_change = round((cur_comp_muni_rate/100)*(eav-exe_homeowner) - (new_comp_muni_rate/100*eav)) ) %>% # # # & (cur_comp_TC_rate*(eav-exe_homeowner) - (new_comp_TC_rate*eav) > 100)# filter(between(bill_change, -5, 5)) %>%# select(agency_name, bill_change, av, eav, everything()) %>% arrange(agency_name)pin_data2 %>%ungroup() %>%filter(class >199& class <300) %>%filter(final_tax_to_tif ==0) %>%filter( exe_homeowner ==10000) %>%filter(exe_senior ==0& exe_freeze ==0& exe_longtime_homeowner ==0& exe_disabled ==0& exe_vet_returning ==0& exe_vet_dis_lt50 ==0& exe_vet_dis_50_69 ==0& exe_vet_dis_ge70 ==0& exe_abate ==0) %>%select(tax_code:all_exemptions, cur_comp_TC_rate,new_comp_TC_rate, agency_name) %>%left_join(muni_taxrates, by ="agency_name") %>%mutate(bill_current = (final_tax_to_dist.x), # money needed by non-TIF agencies# bill_noexemps = new_comp_muni_rate/100*(equalized_AV.x-all_exemptions+exe_homeowner),bill_noGHE = new_comp_TC_rate/100* (equalized_AV.x-all_exemptions+exe_homeowner), # this uses tax code tax ratebill_change =round(bill_noGHE - bill_current)) %>%mutate(rate_change_muni = cur_comp_muni_rate - new_comp_muni_rate, # muni level, not tax code levelnobillchange_propertyEAV =round(10000* ((cur_comp_muni_rate/100) / (rate_change/100))),nobillchange_propertyEAV_munirates =round(exe_homeowner * ((cur_comp_muni_rate/100) / (rate_change_muni/100))),nochange_av_munirates =round(nobillchange_propertyEAV_munirates / eq_2021),rate_change_TC = cur_comp_TC_rate - new_comp_TC_rate, # tax code levelnobillchange_propertyEAV_TCrates =round(exe_homeowner * ((cur_comp_TC_rate/100) / (rate_change_TC/100))),nochange_av_TCrates =round(nobillchange_propertyEAV_TCrates / eq_2021), ) %>%filter(between(bill_change, -5, 5)) %>%select(clean_name, bill_change, pin_AV, nobillchange_propertyEAV_TCrates, nobillchange_propertyEAV_munirates, nochange_av_munirates, nochange_av_TCrates,#nochange_av, nochange_av2, eav.x, # everything() ) #%>% arrange(agency_name, av)# # pin_data2 %>% # ungroup() %>%# filter(class > 199 & class < 300) %>% # filter(has_HO_exemp == 1 & pin_AV > 25 & exe_homeowner == 10000) %>% # filter(exe_senior == 0 & # exe_freeze == 0 & # exe_longtime_homeowner == 0 & # exe_disabled == 0 & # exe_vet_returning == 0 & # exe_vet_dis_lt50 == 0 & # exe_vet_dis_50_69 == 0 & # exe_vet_dis_ge70 == 0 & # exe_abate == 0) %>%# select(class:tif_share, year:exe_abate, all_exemptions, agency_name) %>%# left_join(muni_taxrates, by = "agency_name") %>% # # mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>% # # mutate(bill_current = (final_tax_to_dist.x), # money needed by non-TIF agencies# bill_noexemps = new_comp_muni_rate/100*(equalized_AV.x-all_exemptions+exe_homeowner),# bill_change = round(bill_noexemps - bill_current)) %>%# mutate(rate_change = cur_comp_muni_rate - new_comp_muni_rate,# nobillchange_propertyEAV = round(10000 * ((cur_comp_muni_rate/100) / (rate_change/100))),# # nobillchange_propertyEAV2 = round(exe_homeowner * ((cur_comp_TC_rate/100) / (rate_change/100))),# # nochange_av = round(nobillchange_propertyEAV / eq_2021),# # nochange_av2 = round(nobillchange_propertyEAV2 / eq_2021),# # ) %>%# filter(between(bill_change, -5, 5)) %>% # select(clean_name, bill_change, pin_AV, av.x, nochange_av,# nochange_av2, # eav.x, everything()) #%>% arrange(agency_name, av)```> have to also filter for properties that claimed the full exemption amount otherwise get multiple "breakeven points"> Why are there PINs that have \$0 paid to nonTIF agencies and tax bills that go completely to TIF agencies?```{r muni-taxrate-range}pin_data2 %>%group_by(agency_name) %>%summarize(min_rate =min(tax_code_rate),max_rate =max(tax_code_rate),mean_rate =mean(tax_code_rate),rate_range = max_rate - min_rate, )%>%left_join(nicknames) %>%select(clean_name, rate_range, min_rate, max_rate, mean_rate) %>%arrange(-rate_range)```### Calculating breakeven points```{r}#| label: munibreakevenpoints#| tbl-cap: "Statistics calculated using major class 2 properties, outside of TIF areas, that claimed full GHE amount (10,000 EAV) and no other exemption types"muni_breakeven_points <- pin_data2 %>%ungroup() %>%filter(class >199& class <300) %>%#filter(final_tax_to_tif == 0) %>%filter(exe_homeowner ==10000) %>%filter(exe_senior ==0& exe_freeze ==0& exe_longtime_homeowner ==0& exe_disabled ==0& exe_vet_returning ==0& exe_vet_dis_lt50 ==0& exe_vet_dis_50_69 ==0& exe_vet_dis_ge70 ==0& exe_abate ==0) %>%select(av, tax_code:all_exemptions, cur_comp_TC_rate,new_comp_TC_rate, agency_name) %>%left_join(muni_taxrates, by ="agency_name") %>%group_by(clean_name) %>%summarize(median_AV =median(pin_AV),bill_current =mean(final_tax_to_dist.x), # money needed by non-TIF agencies# bill_noexemps = new_comp_muni_rate/100*(equalized_AV.x-all_exemptions+exe_homeowner),bill_noGHE =mean(new_comp_TC_rate/100* (equalized_AV.x-all_exemptions+exe_homeowner)), # this uses tax code tax ratebill_change =round(mean(bill_noGHE - bill_current)),rate_change_muni =mean(cur_comp_muni_rate - new_comp_muni_rate), # muni level, not tax code levelrate_change_TCs =mean(cur_comp_TC_rate - new_comp_TC_rate),nobillchange_propertyEAV =round(mean(10000* ((cur_comp_muni_rate/100) / (rate_change/100)))),nobillchange_propertyEAV_munirates =round(mean(exe_homeowner * ((cur_comp_muni_rate/100) / (rate_change_muni/100)))),nochange_av_munirates =round(mean(nobillchange_propertyEAV_munirates / eq_2021)),# rate_change_TC = cur_comp_TC_rate - new_comp_TC_rate, # tax code level# nobillchange_propertyEAV_TCrates = round(exe_homeowner * ((cur_comp_TC_rate/100) / (rate_change_TC/100))),# nochange_av_TCrates = round(nobillchange_propertyEAV_TCrates / eq_2021),ratio_av_to_breakeven =mean(median_AV / nochange_av_munirates) ) %>%#filter(between(bill_change, -5, 5)) %>% select(clean_name, median_AV, ratio_av_to_breakeven, bill_change, nobillchange_propertyEAV_munirates, nochange_av_munirates, rate_change_muni, rate_change_TCs)```## Assessed Value PIN distribution```{r pindistrib-facetwrap, eval=FALSE}pin_data2 %>%filter((class >199& class <300) & agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%group_by(agency_name) %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +# geom_vline(aes(xintercept = median(av, na.rm=TRUE), color = "red")) +theme_classic()+theme(legend.position ="none") +# geom_text(aes(x=(median(av) + 5000), y = 1500, label = median(av))) +scale_x_continuous(limits =c(-5, 50000)) +# scale_y_continuous(limits = c(0, 1500))+labs(title ="PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs") +facet_wrap(~agency_name)``````{r eval=FALSE}pin_data2 %>%filter((class >199& class <300) & agency_name =="VILLAGE OF PHOENIX") %>%group_by(agency_name) %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +geom_vline(aes(xintercept =median(av, na.rm=TRUE), color ="red")) +geom_vline(aes(xintercept =7974), linetype ="dashed", label ="Breakeven AV") +theme_classic()+theme(legend.position ="none") +geom_text(aes(x=(median(av)), y =450, label =median(av))) +geom_text(aes(x=14000, y =350), label ="Breakeven AV = $7,974") +scale_x_continuous(limits =c(-5, 50000)) +# scale_y_continuous(limits = c(0, 1500))+labs(title ="Phoenix PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs",caption ="Median AV is $3,502, Breakeven AV is $7,974")``````{r chicago-pindistribution, eval = FALSE}pin_data2 %>%filter((class >199& class <300) & agency_name =="CITY OF CHICAGO") %>%ggplot() +geom_histogram(aes(x = av, bins =100)) +geom_vline(aes(xintercept =median(av, na.rm=TRUE), color ="red")) +geom_vline(aes(xintercept =84228), linetype ="dashed") +theme_classic()+theme(legend.position ="none") +geom_text(aes(x=median(av)+20000, y =300000, label =median(av))) +geom_text(aes(x=100000, y =200000), label ="Breakeven AV = $84,228") +scale_x_continuous(limits =c(-5, 500000), labels = scales::dollar) +scale_y_continuous(limits =c(0, 300000))+labs(title ="Chicago PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs",caption ="Median AV is ~$21,000, Breakeven AV is ~$84,000") ```### How many homeowners would have decreased taxbills if the GHE were eliminated?```{r}#| label: tbl-pincount1#| tbl-cap: "Count of PINs with AV > breakeven point. Property classes 200-299."pin_data2 %>%filter((class >199& class <300) ) %>%left_join(muni_taxrates, by ="agency_name") %>%left_join(muni_breakeven_points, by ="clean_name") %>%filter(pin_AV > nochange_av_munirates) %>%group_by(clean_name, has_HO_exemp) %>%summarize(n()) %>%mutate(has_HO_exemp =ifelse(has_HO_exemp ==0, "Did not claim", "Claimed GHE"))``````{r }#| label: tbl-pct-homeowners-benefiting#| tbl-cap: "Count of PINs with AV > breakeven point. Property classes 200 - 299 that claimed GHE in 2021"pin_data2 %>%filter((class >199& class <300) & (has_HO_exemp ==1)) %>%left_join(muni_taxrates, by ="agency_name") %>%left_join(muni_breakeven_points, by ="clean_name") %>%filter(pin_AV > nochange_av_munirates) %>%group_by(agency_name) %>%summarize(pins_over_breakpoint =n()) %>%left_join(muni_taxrates) %>%mutate(pct_c2_benefits =round((pins_over_breakpoint / C2_pins_in_muni)*100, digits =4)) %>%select(clean_name, pins_over_breakpoint, pct_c2_benefits)``````{r}#| label: tbl-whobenefits#| tbl-cap: "Count of PINs with AV > breakeven point. Property classes 200 - 299 that did not claim the GHE in 2021"#| pin_data2 %>%filter((class >199& class <300) & has_HO_exemp ==0) %>%left_join(muni_taxrates, by ="agency_name") %>%group_by(agency_name) %>%summarize(lowerbill_PINcount =n() ) %>%left_join(muni_taxrates) %>%mutate(pct_c2_benefits =round((lowerbill_PINcount / C2_pins_in_muni)*100, digits =4)) %>%select(clean_name, lowerbill_PINcount, pct_c2_benefits) %>%arrange(pct_c2_benefits)``````{r}#| label: tbl-whobenefits2#| tbl-cap: "Count of PINs that claimed GHE with AV below the breakeven point. These properties would experience an increased tax bill if the GHE were removed."pin_data2 %>%filter(class >199& class <300) %>%left_join(muni_taxrates, by ="agency_name") %>%left_join(muni_breakeven_points, by ="clean_name") %>%filter(pin_AV < nochange_av_munirates & has_HO_exemp ==1) %>%group_by(agency_name) %>%summarize(under_breakpoint =n()) %>%left_join(muni_taxrates) %>%mutate(pct_c2_w_higherbills =round((under_breakpoint / C2_pins_in_muni)*100, digits =4)) %>%select(clean_name, under_breakpoint, pct_c2_w_higherbills) %>%arrange(clean_name)``````{r }#| label: tbl-whobenefits3#| tbl-cap: "Count of PINs where current tax bill < hypothetical tax bill"homeowners_higherbills <- pin_data2 %>%filter(class >199& class <300) %>%filter((cur_comp_TC_rate*(eav-exe_homeowner) < (new_comp_TC_rate*eav))) %>%group_by(agency_name, ) %>%summarize(higherbill_PINcount =n()) %>%left_join(muni_taxrates, by ="agency_name") %>%select(clean_name, higherbill_PINcount, C2_pins_in_muni) %>%mutate(pct_C2_higherbills = higherbill_PINcount / C2_pins_in_muni)homeowners_higherbills %>%arrange(clean_name)``````{r}## Number of residential PINs that would have a lower tax bill in each munihomeowners_benefited <- pin_data2 %>%filter(class >199& class <300) %>%# filter keeps obs if they didn't claim the exemption OR they did claim it & have fancy houses# filter(has_HO_exemp == 0 | (has_HO_exemp == 1 & pin_AV > nochange_av)) %>%filter(# did not claim the general homeowner exemption (GHE) has_HO_exemp ==0|# OR # did claim the GHE in 2021, but their new bill still goes down compared # to than their current tax bill if the GHE were removed # implies that the hypothetical taxrate change*eav > exempt EAV * current tax rate (has_HO_exemp ==1& (cur_comp_TC_rate*(eav-exe_homeowner) > (new_comp_TC_rate*eav)))) %>%group_by(agency_name, has_HO_exemp) %>%summarize(lowerbill_PINcount =n()) %>%left_join(muni_taxrates, by ="agency_name") %>%mutate(lowerbill_PINcount =ifelse(is.na(lowerbill_PINcount), 0, lowerbill_PINcount)) %>%pivot_wider(id_cols =c(clean_name, C2_pins_in_muni), names_from = has_HO_exemp, values_from = lowerbill_PINcount, ) %>%rename(didnot_claim =`0`,lowerbill_did_claim_GHE =`1`) %>%mutate(lowerbill_did_claim_GHE =ifelse(is.na(lowerbill_did_claim_GHE), 0, lowerbill_did_claim_GHE),lowerbill_PINcount = didnot_claim + lowerbill_did_claim_GHE,pct_c2_w_higherbills =100-round((lowerbill_PINcount / C2_pins_in_muni)*100, digits =2),pct_c2_w_lowerbills =round((lowerbill_PINcount / C2_pins_in_muni)*100, digits =2),pct_c2_lowerbills_did_claim =round((lowerbill_did_claim_GHE / C2_pins_in_muni)*100, digits =2),pct_c2_lowerbills_didnotclaim =round((didnot_claim / C2_pins_in_muni)*100, digits =2))# homeowners_benefited``````{r}#| label: tbl-whobenefits-comparison#| tbl-cap: "Comparison of increased and decreased tax bills"homeowners_benefited %>%arrange(clean_name)homeowners_higherbills %>%arrange(clean_name)#homeowners_benefited %>% write_csv("../Output/homeowner_billchanges.csv")```### Property Value where Bill doesn't change```{r}#| layout-ncol: 2#| column: screen-inset#| nobillchange <- muni_taxrates %>%left_join(muni_breakeven_points, by ="clean_name") %>%rename(nobillchange_propertyEAV = nobillchange_propertyEAV_munirates,nochange_av = nochange_av_munirates)nobillchange %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA", "CITY OF CHICAGO")) %>%ggplot(aes(y=C2median_av, x = agency_name)) +geom_col()+geom_text(aes(y=C2median_av +3000, label =round(C2median_av) ) ) +scale_y_continuous(labels = scales::dollar) +theme_classic() +scale_x_discrete(label =c("Chicago","Park Forest", "Phoenix", "Riverdale", "Winnetka")) +labs(y ="Median Residential AV", x ="", title ="Median Residential Property Assessed Value - All Class 2 property Types")nobillchange %>%filter(agency_name %in%c("CITY OF CHICAGO", "VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%ggplot(aes(y=(nobillchange_propertyEAV/eq_2021), x = agency_name)) +geom_col() +geom_text(aes(y=(nobillchange_propertyEAV/eq_2021) +3000, label =round(nobillchange_propertyEAV/eq_2021)) ) +scale_y_continuous(labels = scales::dollar) +theme_classic() +scale_x_discrete(label =c("Chicago", "Park Forest", "Phoenix", "Riverdale", "Winnetka")) +labs(y ="Breakeven Point - AV", x ="", title ="Residential Property AV Breakeven Point", caption ="Residential properties above these values would have their bills decrease if the GHE were eliminated (if they had claimed the GHE before)")``````{r}#| label: fig-breakevenToAV-notfiltered#| column: margin#| fig-cap: "Includes all municipalities"nochange <- C2_munistats %>%left_join(muni_taxrates) %>%left_join(muni_breakeven_points) %>%rename(nobillchange_propertyEAV = nobillchange_propertyEAV_munirates,nochange_av = nochange_av_munirates)nochange %>%# filter(nochange_av < 300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +# geom_smooth(method = "lm" )+geom_text(aes(y = (nochange_av-5000), x = (C2median_av)), size =2)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none")+labs(y =" Breakeven Point", x ="Median AV - Class 2 Properties in Municipality", title ="Some highly valued homes would have lower tax bills if the GHE were eliminated", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated.")``````{r}#| label: fig-breakevenToAV-filtered-labeled#| fig-cap: "Excludes outliers: labeled"#| column: marginnochange %>%filter(nochange_av <300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +geom_point(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale","Dolton"))), aes(y = nochange_av, x = C2median_av, color ="red"), size =3) + ggrepel::geom_label_repel(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale", "Dolton"))), aes(y = (nochange_av), x = (C2median_av)), size =3)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none") +labs(title ="Municipalities' Median AV & Breakeven Point - Class 2 Properties Only",y ="Breakeven Point", x ="Median AV")``````{r}#| label: fig-breakevenToAV-filtered#| fig-cap: "Excludes outliers"nochange %>%filter(nochange_av <300000) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point(aes(alpha = .5)) +geom_point(data = (nochange %>%filter(clean_name %in%c("Park Forest", "Chicago", "Winnetka", "Riverdale","Dolton"))), aes(y = nochange_av, x = C2median_av, color ="red"), size =3) +#ggrepel::geom_text_repel(aes(label = clean_name), vjust = -1,colour="black") + # geom_smooth(method = "lm" )+# ggrepel::geom_text_repel(data = (nochange %>% filter(clean_name %in% c("Park Forest", "Chicago", "Winnetka", "Riverdale", "Dolton"))), aes(y = (nochange_av), x = (C2median_av)), size = 3)+theme_classic() +scale_x_continuous(labels = scales::dollar) +scale_y_continuous(labels = scales::dollar) +theme(legend.position ="none") +labs(title ="Municipalities' Median AV & Breakeven Point - Class 2 Properties Only",y ="Breakeven Point", x ="Median AV", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated. Excludes outliers: University Park, Bedford Park, McCook, Hodgkins, and Rosemont. Class 2 properties that had claimed the GHE would have lower taxbills even if the GHE were removed if the AV is above the breakeven point.")``````{r}#| label: fig-breakevenToAV-casestudfies#| fig-cap: "Do tax bills still counter-intuitively fall when exemptions are eliminated: The case of Phoenix, Park Forest, and Riverdale"nochange %>%filter(clean_name %in%c("Phoenix", "Park Forest", "Riverdale")) %>%ggplot(aes(y=nochange_av, x = C2median_av, label=clean_name )) +geom_point() +geom_text(aes(y = (nochange_av-500), x = (C2median_av-100)), size =3)+theme_classic() +scale_x_continuous(labels = scales::dollar, limits =c(0,10000)) +scale_y_continuous(labels = scales::dollar, limits =c(0,20000)) +labs(title ="Municipalities' Median AV & Breakeven Point",y =" Breakeven Point", x ="Median AV", caption ="The breakeven point is the assessed values at which a major class 2 property would not have their taxbill change if the GHE were eliminated. Excludes outliers: University Park, Bedford Park, McCook, Hodgkins, and Rosemont. Class 2 properties that had claimed the GHE would have lower taxbills even if the GHE were removed if the AV is above the breakeven point.")``````{r}# nochange %>%# filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE") ) %>%# filter(is.finite(nobillchange_propertyEAV) )%>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# geom_point()+# geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+500)), size = 2) +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# theme_classic() +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners have lower tax bills if the GHE exemption was ended?",# subtitle = "Yes, in very, very rare cases. ")# # # nochange %>%# # filter(agency_name == "VILLAGE OF WINNETKA") %>%# filter(median_eav > 150000) %>%# filter(is.finite(nobillchange_propertyEAV) ) %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# #filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# geom_point()+# geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 2) +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# theme_classic() +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners with GHE benefit from removal of the GHE?",# caption = "A $500K home in Winnetka would benefit if everyone else lost their General Homestead Exemption")# nochange %>%# filter(is.finite(nobillchange_propertyEAV) ) %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=clean_name)) +# # geom_smooth(method = "lm" )+# geom_text(aes(x = (nobillchange_propertyEAV), y = (median_eav+1000)), size = 2)+# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# scale_y_continuous(labels = scales::dollar) +# labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners who claim GHE benefit from removal of the GHE?",# subtitle = "In very, very rare instances: yes. ")# # nochange %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# # filter(nobillchange_propertyEAV < 7000000) %>% # ggplot(aes(y=rate_change, x = median_eav, label=clean_name)) +# geom_point() +# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# labs(x= "EAV of Class 2 Property w/ No Bill Change", y = "Composite Tax Rate Change (Percentage Points)", title = "EAV of Residential Property 'Breakeven Point' & Composite Tax Rate Change # from Elimination of GHE")nochange %>%select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%ggplot(aes(y=rate_change, x = C2median_av, label=clean_name)) +geom_point() +theme_classic() +scale_x_continuous(labels = scales::dollar) +labs(x="AV of Class 2 Property w/ No Bill Change", y ="Composite Tax Rate Change (Percentage Points)", title ="AV of Residential Property 'Breakeven Point' & Composite Tax Rate Change from Elimination of GHE")# # nochange %>%# select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%# ggplot(aes(y=rate_change, x = C2median_av*10, label=agency_name)) +# geom_point() +# theme_classic() +# scale_x_continuous(labels = scales::dollar) +# labs(x= "Market Value of Class 2 Property # at 'Breakeven Point'", # y = "Composite Tax Rate Change # (Percentage Points)", # title = "Market Value of Residential Property 'Breakeven Point' & # Composite Tax Rate Change from Elimination of GHE")```## Class 2 Summary Tables```{r}#| column: screen-inset#| tax_bill_change_HO <- pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))tax_bill_change_HOpin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%mutate(median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))## Grouped by if they have a $0 tax bill and had the GHE per munipin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%#filter(class == 203) %>% filter(class >199& class <300) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, zero_bill, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),median_bill_cur =round(median(bill_current)),median_bill_new =round(median(bill_noexemps)),median_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe))```### Calculation for Median properties in each munis```{r}## Grouped by if they have a $0 tax bill and had the GHE per muni## Recalculating for Josh & Rachaelmuni_median_summarytable <- pin_data2 %>%# filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%#filter(class == 203) %>% filter(class >199& class <300) %>%# merge in muni residential median AVleft_join(C2_munistats_filtered) %>%# +/- 500 from municpalities median residential AVfilter(av+500> median_av | av-500< median_av) %>%# Removes properties that received other types of exemptionsfilter(exe_senior ==0& exe_freeze ==0& exe_longtime_homeowner ==0& exe_disabled ==0& exe_vet_returning ==0& exe_vet_dis_lt50 ==0& exe_vet_dis_50_69 ==0& exe_vet_dis_ge70 ==0& exe_abate ==0) %>%arrange(av) %>%mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, # zero_bill, has_HO_exemp ) %>%summarize(AV =median(median_av),`Taxable EAV`=median(eav),bill_cur =round(median(bill_current)),bill_new =round(median(bill_noexemps)),bill_change =round(median(bill_change)),pincount=n(),perceived_savings =median(tax_amt_exe)) %>%# merge in clean_names variableleft_join(nicknames) %>%select(clean_name, has_HO_exemp, bill_cur, bill_new, bill_change, perceived_savings, AV, `Taxable EAV`, everything())```## Tax Bill Distributions```{r}#| layout-ncol: 2pin_data2 %>%filter(major_class_code ==2& bill_current <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Actual Tax Bills With Current Exemptions", y ="Number of Tax Bills")pin_data2 %>%group_by(has_HO_exemp) %>%filter(major_class_code ==2& bill_current <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Actual Tax Bills With Current Exemptions", y ="Number of Tax Bills") +facet_wrap(~has_HO_exemp)pin_data2 %>%filter(major_class_code ==2& bill_noexemps <30000& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000), label = scales::dollar) +scale_y_continuous(limits =c(0, 1500), label = scales::comma)+labs(x="PF's Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills")``````{r}#| label: fig-billdistributions-phoenix#| layout-ncol: 2pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000)) +scale_y_continuous(limits =c(0, 600))+labs(title="PHOENIX's Current Tax Bill Distribution", x ="Dollars", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-5,20000)) +scale_y_continuous(limits =c(0, 600))+labs(title="PHOENIX's Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills")``````{r}#| label: fig-billdist-winnetka#| layout-ncol: 2pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_current, bins =100))+geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +# scale_x_continuous(limits = c(-5,20000)) +# scale_y_continuous(limits = c(0, 80000)) +labs(title="WINNETKA's Actual Tax Bills With Current Exemptions", x="Dollars", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_noexemps, bins =100))+geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +#scale_x_continuous(limits = c(-5,20000)) + #scale_y_continuous(limits = c(0, 80000)) +labs(title="WINNETKA's Tax Bills Without General Homestead Exemptions", x="Dollars", y ="Number of Tax Bills")``````{r}#| label: fig-bill-change#| layout-ncol: 2pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PARK FOREST") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +# scale_y_continuous(limits = c(0, 2000))+labs(title="Park Forest's Change in Tax Bills", subtitle ="Hypothetical with no General Homestead Exemptions", x ="Change in Tax Bill ($)", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF PHOENIX") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +# scale_x_continuous(limits = c(-2800,2800)) + # scale_y_continuous(limits = c(0, 2000))+labs(title ="PHOENIX's Change in Tax Bills", subtitle ="Hypothetical with no General Homestead Exemptions", x ="Change in Tax Bill ($)", y ="Number of Tax Bills")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF WINNETKA") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +labs(title="WINNETKA's Change in Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills", x ="Dollars")pin_data2 %>%filter(major_class_code ==2& agency_name =="VILLAGE OF RIVERDALE") %>%ggplot() +geom_histogram(aes(x= bill_change, bins =100))+geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color ="red")) +theme_classic()+theme(legend.position ="none") +scale_x_continuous(limits =c(-10000,10000)) +labs(title="RIVERDALE's Change in Tax Bills Without General Homestead Exemptions", y ="Number of Tax Bills", x ="Dollars")``````{r}#| label: tbl-summarytables#| pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, zero_bill, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name, has_HO_exemp) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())pin_data2 %>%filter(pin_AV >0) %>%filter(agency_name %in%c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA","VILLAGE OF RIVERDALE")) %>%filter(class >199& class <300) %>%arrange(av) %>%# group_by(agency_name, has_HO_exemp) %>% mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,# current bill = current tax rate * portion of levy billedbill_current = (final_tax_to_dist + final_tax_to_tif),bill_noexemps = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),bill_change = bill_noexemps - bill_current) %>%group_by(agency_name) %>%summarize(median_AV =median(av),median_EAV =median(eav),mean_bill_cur =mean(bill_current, na.rm=TRUE),median_bill_cur =median(bill_current),mean_bill_new =mean(bill_noexemps, na.rm=TRUE),median_bill_new =median(bill_noexemps),mean_change =mean(bill_change, na.rm=TRUE),median_change =median(bill_change),perceived_savings =median(tax_amt_exe),cur_comp_TC_rate =mean(cur_comp_TC_rate),new_comp_TC_rate =mean(new_comp_TC_rate),pincount=n())```